import json
import os

import numpy as np
import matplotlib.pyplot as plt
import openpyxl

# 给数据一列设置单元格样式,这个是设置3色渐变
def set_condition_format3():
    import openpyxl
    from openpyxl.formatting.rule import ColorScaleRule

    # file_name = "C:/Users/Dell/Documents/PythonFiles/forTESTonly/2sel/base/new_xlsx/changeDB318.xlsx"
    # file_name = "C:/Users/Dell/Documents/PythonFiles/forTESTonly/2sel/base/new_xlsx/result.xlsx"
    file_name = "2selCELL/result.xlsx"
    wb = openpyxl.load_workbook(file_name)

    for ws in wb:
        rowN = ws.max_row

        for c in range(20):     # 一共有21列，其中，第一列不比较
            BT = chr(ord("B") + c)
            temp_range = f'{BT}2:{BT}{rowN}'
            if "small" in ws[f"{BT}1"].value:   # 判断列名，若是越小越好
                big, small = '27a500' ,'ff564e'       # 小的是绿色
                mid_value = 10
            else:
                big, small = 'ff564e' , '27a500'    # 大的是绿色
                mid_value = 90
            ws.conditional_formatting.add(temp_range,
                                          ColorScaleRule(start_type='max', start_color=big,
                                                         mid_type='percentile', mid_value=mid_value,  mid_color = 'FFFFFF',
                                                         end_type='min', end_color=small
                                                         )
                                        )
    wb.save(file_name)
# set_condition_format3()
#
# print("ok")



#  这个函数,是之前用于分类一个模型不同阶段结果
#  做法是,将一张图片,它的各个阶段,放在一个文件夹中
# 下面是分类115张图片，生成的compare_results文件夹
# 这里有改正
def classify_pics(path):
    import os
    import shutil

    father_path = path
    sub_dirs, file_names = None, None  # 子文件夹名, 每个子文件夹都有的文件名
    for root, dirs, files in os.walk(father_path):
        if not sub_dirs and dirs:
            sub_dirs = dirs
        if not file_names and files:
            file_names = files
            break


    def mkdir(dir):
        "这个函数创建不存在的 路径文件夹"
        import os
        if not os.path.exists(dir):
            os.makedirs(dir)


    target_father_path = father_path.rsplit("/",maxsplit=1)[0] +  "/按图片名查看图片/"  # 在上级文件夹中创建分类图片文件夹的父文件夹
    for p in file_names:  # 遍历图片名，有多少个图片，就复制多少次
        print("当前正在移动", p)
        # 准备当前图片的目标文件夹
        target_dir_name = os.path.splitext(p)[0]
        target_dir = target_father_path + target_dir_name
        mkdir(target_dir)  # 创建放分类图片的文件夹

        # 进行移动
        for dir in sub_dirs:  # 遍历原有的子文件夹
            file_path = os.path.join(father_path, dir, p)
            # 注意，这里名字要改一下，不然同名文件会覆盖前面的。这里按文件夹名来命名
            new_pic_name = dir + os.path.splitext(p)[1]
            target_file_path = os.path.join(target_dir, new_pic_name)

            shutil.copyfile(file_path, target_file_path)  # 复制


# abs_path = "D:/ForFullSavedModel/Use7GModels/1117/compare_results"
# classify_pics(abs_path)




# 改良前面的，对每个单元格的数据进行比较 20211203之后
# 生成的会是一个json文件rank.json，模型名：115*20的列表
# 列表中，放的是我的该模型的指标在目前给出的最佳值中的排名。
# 用于比较的函数，也是一个json文件，命名为BESTofCELL.json

import json
def convert2list11520(jsondata:list):
    '''将excel生成的json数据转换成115*20列表'''
    lst = [list(row.values()) for row in jsondata]
    return lst

def appendCELLbest():
    # 这个是手工制作的，用\t分割的json,值还是粘在一起的
    with open("2selCELL/base/29.json", "r", encoding="utf8") as f:
        data = json.loads(f.read())
        data = convert2list11520(data)
        return data
def append_my(name):
    # 这个是手工制作的，用\t分割的json,值还是粘在一起的
    with open(name,"r",encoding="utf8") as f:
        data = json.loads(f.read())
        data = convert2list11520(data)
        return data
def let_myBEST_21():
    # 对115*20的小格子，如果我的指 标是最好的，就让它成为1(to1)
    import os
    file_names = os.listdir("myresults")
    file_names.sort(key=lambda x:int(x.split('(')[0]))

    # 下面都是自动执行的
    metrics = ['sen', 'me', 'mse(small)', 'avg', 'std', 'vifp', 'snr', 'min', 'uqi', 'qabf', 'vif', 'ssim', 'psnr', 'ifc', 'q0i', 'ce(small)', 'edge', 'qcv(small)', 'sf', 'qcb']
    #  把自己的模型和指标平均值添加一下
    best = appendCELLbest()    # 是一个115*20
    results = {}

    sel_model_and_pics = {}
    for name in file_names:
        my = append_my("myresults/" + name)


        sel_picNo = []

        best115 = []      # 结果会是一个列表，对应每个图片的指标是不是最佳
        # 还应该有一个，每个图片，
        for i in range(115):
            row_best = []
            for j,m in enumerate(metrics):
                if "small" not in m :
                    if my[i][j] >= best[i][j] :
                        row_best.append(1)
                    else:
                        row_best.append(0)
                else:
                    if my[i][j] <= best[i][j] :
                        row_best.append(1)
                    else:
                        row_best.append(0)
            best115.append(row_best)

            if sum(row_best) >= 3:       # 如果这个图片可以使3个指标最优，那就保留作为选中的数据集
                sel_picNo.append(i+1)

        results[name[:-5]]=best115
        if len(sel_picNo) >= 25:
            sel_model_and_pics[name[:-5]] = sorted(sel_picNo)

    with open("2selCELL/rank.json","w",encoding="utf8") as f:
        f.write(json.dumps(results))
    with open("2selCELL/prime_pics.json","w",encoding="utf8") as f:
        f.write(json.dumps(sel_model_and_pics))
# let_myBEST_21()
# print("OK")



def paint2pixCELL():
    """
    将一张图片上，自己的表现指标最好的点标记出来
    """
    base_path = "2selCELL/base/"
    path = "2selCELL/rank.json"
    with open(path,"r",encoding="utf8") as f:
        data = json.loads(f.read())
        for i,(model,pic) in enumerate(data.items()):
            if i % 5 == 0:
                plt.figure(figsize=(110, 21))
                fig, axs = plt.subplots(nrows=1, ncols=5, figsize=(20, 20))

            ax = axs[i%5]
            ax.set_title(model)
            ax.imshow(pic, cmap='Greens')
            if i % 5 == 4 or i == len(data) -1:
                plt.savefig(f'{base_path}第{i // 5 + 1}张图.png')
                # plt.show()
                # input()
# paint2pixCELL()
# print("OK")


def makeCELLxlsx():
    "用于将结果保存在excel中，方便分析"

    base_path = "2selCELL/base/"
    path = "2selCELL/rank.json"
    with open(path, "r", encoding="utf8") as f:
        data = json.loads(f.read())

    from openpyxl.styles import PatternFill
    style = PatternFill("solid", fgColor="73ff34")

    # 创建新表
    new_wb = openpyxl.Workbook()
    for i, (name, best115) in enumerate(data.items()):
        new_sheet = new_wb.create_sheet(title=name)
        # 添加标题
        metrics = ['sen', 'me', 'mse(small)', 'avg', 'std', 'vifp', 'snr', 'min', 'uqi', 'qabf', 'vif', 'ssim', 'psnr',
                   'ifc', 'q0i', 'ce(small)', 'edge', 'qcv(small)', 'sf', 'qcb',"求和"]
        new_sheet.append(metrics)

        # 添加源数据
        for index,row_best in enumerate(best115):
            row_best.append(f"=SUM(A{index+2}:T{index+2})")
            new_sheet.append(row_best)

            for colN,best in enumerate(row_best[:-1]):    # 加这个循环是为了染色
                if best:
                    new_sheet[f"{chr(ord('A') + colN)}{index+2}"].fill = style  # 填充单元格

        # 最后一行求平均值
        avg_formula = [f"=SUM({chr(ord('A') + off)}2:{chr(ord('A') + off)}116)" for off in range(ord('T')-ord('A')+1)]
        new_sheet.append(avg_formula)

        # xlsx_path = base_path + "rank.xlsx"
        xlsx_path = base_path + "rank.xlsx"
        new_wb.save(xlsx_path)  # 写入,保存为模型名

# makeCELLxlsx()
#
# print("OK")



def writCELLsheetcmp():
    file_name = '2selCELL/base/rank.xlsx'
    wb = openpyxl.load_workbook(file_name)
    sheet = wb["Sheet"]

    thead = ["模型名", "5个最好","4个最好","3个最好"]
    sheet.append(thead)

    for name in wb.sheetnames[1:]:
        row = [name]
        row.extend([f"""=COUNTIF('{name}'!U2:'{name}'!U116,">={i}")""" for i in [5,4,3]])
        sheet.append(row)
    wb.save(file_name)
# writCELLsheetcmp()
# print("OK")




from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
def setSHEEThw(ws,width,height):
    for i in range(1, ws.max_row + 1):
        ws.row_dimensions[i].height = height
    for i in range(1, ws.max_column + 1):
        ws.column_dimensions[get_column_letter(i)].width = width

def setHeightWidth(width=4,height=12):
    path = "2selCELL/base/rank.xlsx"
    wb = load_workbook(path)
    sheetnames = wb.sheetnames

    for name in sheetnames:
        ws = wb.get_sheet_by_name(name)
        setSHEEThw(ws, width, height)

    wb.save(path)      # 另存为：原文件名3.xlsx

# setHeightWidth()
# print("ok")


def extract_rows_and_cmp_with_7NN():
    import json
    import openpyxl

    def write_cmp_sheet(new_wb, sheet_names, avg_row_pos):
        cmp_sheet = new_wb["cmp"]

        for index, name in enumerate(sheet_names):
            if not index:  # 添加表名,这个应该是叫Sheet的空表单
                m = ["model"]
                for cell in new_wb[name][1]:
                    m.append(cell.value)
                cmp_sheet.append(m)

            avg_from = [name]
            avg_from.extend([f"='{name}'!{chr(ord('A') + off)}{avg_row_pos}" for off in
                             range(ord('T') - ord('A') + 1)])
            cmp_sheet.append(avg_from)  # 新sheet添加原表数据

    def write_others_xlsx(new_wb, rows):
        # 打开比较的模型数据
        file_name = "D:/matlab_work/fusion_metrix_results/baseXLSX/29.xlsx"
        wb = openpyxl.load_workbook(file_name)  # 打开文件

        temp_others_name = []

        # 迭代每个sheet,进行取特定行的操作，写入新表
        for sheet in wb:
            temp_others_name.append(sheet.title)
            new_sheet = new_wb.create_sheet(title=sheet.title)  # 新表中创建同名sheet

            # 取源数据。列名的第一行,这儿就不用再次插入了
            for r in rows:
                r_data = []
                for cell in sheet[r]:  # sheet是原表
                    r_data.append(cell.value)
                new_sheet.append(r_data)  # 新sheet添加原表数据

            # 最后一行求平均值
            avg_formula = [f"=AVERAGE({chr(ord('A') + off)}2:{chr(ord('A') + off)}{len(rows)})" for off in
                           range(ord('T') - ord('A') + 1)]
            new_sheet.append(avg_formula)

        return temp_others_name
    def make_xlsx():
        "将自己好的模型的数据源进行整理，根据优势图片的结果来比较"
        # 获取自己的模型名和优势图片编号
        base_path = "2selCELL/"
        with open(base_path + "prime_pics.json", "r", encoding="utf8") as f:
            data = json.loads(f.read())

        # 打开自己的mytestG7依次操作,wb:我的结果
        file_name = "D:/matlab_work/fusion_metrix_results/mytestG7ALL.xlsx"
        wb = openpyxl.load_workbook(file_name)  # 打开文件,read_only设置为True，则无法按列访问单元格。

        # 根据自己的较好的模型的模型名,进行操作
        for name, rows in data.items():
            sheet = wb[name]  # 选中sheet

            # 创建新表
            new_wb = openpyxl.Workbook()
            new_sheet = new_wb.create_sheet(title=name)

            # 取源数据
            rows = list(map(lambda x: x + 1, rows))  # 因为图片行数是从第2行开始的，而图片名是从1开始的。
            rows.insert(0, 1)  # 这是列名的第一行
            for r in rows:
                r_data = []
                for cell in sheet[r]:
                    r_data.append(cell.value)
                new_sheet.append(r_data)

            # 最后一行求平均值
            avg_formula = [f"=AVERAGE({chr(ord('A') + off)}2:{chr(ord('A') + off)}{len(rows)})" for off in
                           range(ord('T') - ord('A') + 1)]
            new_sheet.append(avg_formula)

            # 再把参照模型的数据都进行一下写入
            sheet_names  = write_others_xlsx(new_wb, rows)

            # 先保存文件，再加载，写入cmp的值
            new_wb["Sheet"].title = 'cmp'
            # 再将比较数据放在首页
            sheet_names.append(name)
            write_cmp_sheet(new_wb, sheet_names, len(rows) + 1)

            xlsx_path = base_path + name + ".xlsx"
            new_wb.save(xlsx_path)  # 写入,保存为模型名

        wb.close()

    # 将prime_pics.json中的模型的优势图片挑出来,并将这些图片用于其他NN模型模型,将两者的结果放在一起比较
    make_xlsx()


extract_rows_and_cmp_with_7NN()
print("OK")

# 运行此模块，会将每张图片每个指标的最优模型选出，如果我的模型是第一，那就标为1；
# 模型的结果是一个excel，其中各表单是模型名
# 还会生成若干图片，是以像素为点，表示是否是第一。
# if __name__=="__main__":
#     let_myBEST_21()
#     paint2pixCELL()
#     makeCELLxlsx()
#     writCELLsheetcmp()
#     # setHeightWidth()
#     extract_rows_and_cmp_with_7NN()
#     print("ok")


# 这儿需要先手动将值传递至result.xlsx之后,再调用下面这个函数
# set_condition_format3()